package com.example.springboot.utils.jdbc;

import com.example.springboot.utils.encrypt.AESUtil;
import org.apache.commons.lang.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import java.io.InputStream;
import java.lang.reflect.Field;
import java.sql.*;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.Date;
import java.util.concurrent.locks.Lock;
import java.util.concurrent.locks.ReentrantLock;

/**
 * mysql操作数据库
 *
 * @author admin
 */
public class JdbcUtils {

    private static final Log log = LogFactory.getLog(JdbcUtils.class);

    private static ThreadLocal<Connection> connect = new ThreadLocal<Connection>();
    private static Lock lock = new ReentrantLock();

    // 数据库配置
    private static String URL = "jdbc:mysql://localhost:3306/upms";
    private static String USER_NAME = "root";
    private static String PASSWORD = "dragon";
    private static String FILE_NAME = "application.properties";
    //加密方式
    private static String KEY = "AES";

    static {
        try {
            log.info("开始加载数据库连接配置文件........");
            /** 通过属性文件获取数据库连接的参数值 **/
            Properties properties = new Properties();
            InputStream stream = JdbcUtils.class.getClassLoader().getResourceAsStream(FILE_NAME);
            properties.load(stream);
            /** 获取属性文件中的值 **/
            String driverClassName = properties.getProperty("mysql.jdbc.driver");
            URL = properties.getProperty("mysql.jdbc.url");
            USER_NAME = properties.getProperty("mysql.jdbc.username");
            PASSWORD = properties.getProperty("mysql.jdbc.password");
            if (StringUtils.isNotBlank(PASSWORD)) {
                PASSWORD = AESUtil.AESDecode(PASSWORD, AESUtil.SALT);
            }
            Class.forName(driverClassName);
            log.info("初始化数据库连接完成........");
        } catch (Exception e) {
            e.printStackTrace();
            log.error("加载数据库连接配置文件异常：" + e);
        }
    }

    /**
     * 获取数据库连接
     *
     * @return 数据库连接
     */
    public static Connection getConnection() {
        Connection connection = connect.get();
        try {
            if (connection == null) {
                synchronized (JdbcUtils.class) {
                    if (connection == null) {
                        connection = DriverManager.getConnection(URL, USER_NAME, PASSWORD);
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
            log.error("获取数据库连接失败： " + e);
        }
        return connection;
    }

    /**
     * 获取Statement
     *
     * @return
     */
    public static Statement getStatement() {

        Statement statement = null;
        try {
            statement = getConnection().createStatement();
        } catch (Exception e) {
            e.printStackTrace();
            log.error("获取Statement异常： " + e);
        }
        return statement;
    }

    /**
     * 开启事务
     */
    public static void begin() {
        try {
            Connection con = connect.get();// 获取当前线程的事务连接
            if (con == null)
                throw new SQLException("开启事务失败！");
            con.setAutoCommit(false);// 设置为手动提交
            connect.set(con);// 把当前事务连接放到connect中
        } catch (Exception e) {
            e.printStackTrace();
            log.error("开启事务失败： " + e);
        }
    }

    /**
     * 提交事务
     */
    public static void commit() {
        try {
            Connection con = connect.get();// 获取当前线程的事务连接
            if (con == null) {
                throw new SQLException("没有事务不能提交！");
            }
            con.commit();// 提交事务
            con.close();// 关闭连接
            con = null;// 表示事务结束！
            connect.remove();
        } catch (Exception e) {
            e.printStackTrace();
            log.error("提交事务失败： " + e);
        }
    }

    /**
     * 回滚事务
     */
    public static void rollback() {
        try {
            Connection con = connect.get();// 获取当前线程的事务连接
            if (con == null) {
                throw new SQLException("没有事务不能回滚！");
            }
            con.rollback();
            con.close();
            con = null;
            connect.remove();
        } catch (Exception e) {
            e.printStackTrace();
            log.error("回滚事务失败： " + e);
        }
    }

    /**
     * 关闭连接
     */
    public static void close(Connection con) {
        try {
            if (con != null) {
                con.close();
                con = null;
            }
        } catch (Exception e) {
            e.printStackTrace();
            log.error("关闭数据库连接失败： " + e);
        }
    }

    /**
     * 关闭连接
     */
    public static void close(Statement pstm) {
        try {
            if (pstm != null) {
                pstm.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
            log.error("关闭数据库连接失败： " + e);
        }

    }

    /**
     * 关闭连接
     */
    public static void close(PreparedStatement pstm) {
        try {
            if (pstm != null) {
                pstm.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
            log.error("关闭数据库连接失败： " + e);
        }

    }

    /**
     * 关闭连接
     */
    public static void close(Connection con, PreparedStatement pstm) {
        try {
            close(pstm);
            close(con);
        } catch (Exception e) {
            e.printStackTrace();
            log.error("关闭数据库连接失败： " + e);
        }
    }

    /**
     * 关闭连接
     */
    public static void close(Connection con, Statement pstm) {
        try {
            close(pstm);
            close(con);
        } catch (Exception e) {
            e.printStackTrace();
            log.error("关闭数据库连接失败： " + e);
        }
    }

    /**
     * 释放Connection
     */
    public static void release(Connection connection) {
        try {
            Connection con = connect.get();// 获取当前线程的事务连接
            if (connection != con) {// 如果参数连接，与当前事务连接不同，说明这个连接不是当前事务，可以关闭！
                if (connection != null && !connection.isClosed()) {// 如果参数连接没有关闭，关闭之！
                    connection.close();
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
            log.error("释放Connection失败： " + e);
        }
    }

    /**
     * 查询单条数据，并返回map结果集
     *
     * @param sql ：sql语句
     * @return
     */
    public static Map<String, Object> selectOne(String sql) throws Exception {

        Long startTime = System.currentTimeMillis();
        log.info("查询单条数据sql: " + sql);
        Map<String, Object> map = new HashMap<String, Object>();
        List<Map<String, Object>> list = select(sql);
        if (list != null && list.size() > 0) {
            map = list.get(0);
            return map;
        }
        log.info("查询耗时: " + (System.currentTimeMillis() - startTime));
        return map;
    }

    /**
     * 查询数据
     *
     * @param sql ：sql语句
     * @return
     */
    public static List<Map<String, Object>> select(String sql) throws Exception {
        log.info("查询数据sql: " + sql);
        long startTime = System.currentTimeMillis();
        List<Map<String, Object>> list = executeQuery(sql, null);
        log.info("查询总耗时： " + (System.currentTimeMillis() - startTime));
        return list;
    }

    /**
     * 查询数据
     *
     * @param sql   ：sql语句
     * @param clazz :返回对象
     * @return
     */
    public static <T> T selectOne(String sql, Class<?> clazz) throws Exception {
        log.info("查询数据sql: " + sql);
        List<T> list = select(sql, clazz);
        if (list != null && list.size() > 0) {
            return list.get(0);
        }
        return null;
    }

    /**
     * 查询数据
     *
     * @param sql   ：sql语句
     * @param clazz :返回对象
     * @return
     */
    public static <T> List<T> select(String sql, Class<?> clazz) throws Exception {
        log.info("查询数据sql: " + sql);
        Long startTime = System.currentTimeMillis();
        PreparedStatement pst = null;
        Connection connection = null;
        List<T> list = null;
        try {
            connection = getConnection();
            pst = connection.prepareStatement(sql);
            ResultSet resultSet = pst.executeQuery();
            list = populate(resultSet, clazz);
        } catch (Exception e) {
            e.printStackTrace();
            log.error("查询数据库失败： " + e);
            throw new RuntimeException(e);
        } finally {
            close(connection, pst);
        }
        log.info("查询总耗时： " + (System.currentTimeMillis() - startTime));
        return list;
    }


    /**
     * 将rs结果转换成对象列表
     *
     * @param rs    :结果集
     * @param clazz ：实体类
     * @return
     * @throws Exception
     */
    public static <T> List<T> populate(ResultSet rs, Class<?> clazz) throws Exception {

        List<T> list = new ArrayList<T>();
        ResultSetMetaData rsmd = rs.getMetaData();
        Field[] fields = clazz.getDeclaredFields();
        while (rs != null && rs.next()) {
            @SuppressWarnings("unchecked")
            T obj = (T) clazz.newInstance();// 构造业务对象实体
            for (int index = 1; index <= rsmd.getColumnCount(); index++) {
                String columnName = rsmd.getColumnName(index);
                Object columnValue = rs.getObject(index);

                for (int j = 0; j < fields.length; j++) {
                    Field field = fields[j];
                    // 如果匹配进行赋值
                    if (field.getName().equalsIgnoreCase(columnName)) {
                        boolean flag = field.isAccessible();
                        field.setAccessible(true);
                        field.set(obj, getValue(field, columnValue));
                        field.setAccessible(flag);
                        break;
                    }
                }
            }
            list.add(obj);
        }
        return list;
    }

    /**
     * 将结果转为对应的字段类型
     *
     * @param field ：字段实体类
     * @param value ：字段对应的值
     * @return
     * @throws ParseException
     */
    private static Object getValue(Field field, Object value) {

        try {

            if (value == null || "".equals(value) || "null".equals(value)) {
                return null;
            }
            String columnValue = value.toString().trim();
            Class<?> type = field.getType();
            if (type == byte.class || type == Byte.class) {
                return Integer.parseInt(columnValue);
            } else if (type == int.class || type == Integer.class) {
                String str = columnValue;
                if ("false".equals(str)) {
                    return 0;
                } else if ("true".equals(str)) {
                    return 1;
                } else {
                    return Integer.parseInt(str);
                }
            } else if (type == float.class || type == Float.class) {
                return Float.parseFloat(columnValue);
            } else if (type == double.class || type == Double.class) {
                return Double.parseDouble(columnValue);
            } else if (type == Boolean.class) {
                String str = columnValue;
                boolean parseBoolean = Boolean.parseBoolean(str);
                return parseBoolean;
            } else if (type == String.class) {
                return columnValue;
            } else if (type == Date.class) {
                String str = columnValue;
                SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                Date date = dateFormat.parse(str);
                return date;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 批量操作（新增、修改、删除）,改操作入库较慢
     *
     * @param sql    :查询语句
     * @param params :查询参数集合
     * @return
     */
    public static int[] batch(String sql, List<Object[]> params) throws Exception {
        log.info("批量操作数据sql: " + sql);

        String result = null;
        Long startTime = System.currentTimeMillis();
        Connection conn = null;
        PreparedStatement pstm = null;
        try {
            conn = JdbcUtils.getConnection();
            conn.setAutoCommit(false);
            pstm = conn.prepareStatement(sql);
//            批量操作, 设置 sql使用？占位符参数
            for (int i = 0; i <= params.size(); i++) {
                Object[] objects = params.get(i);
                for (int j = 0; j <= objects.length; j++) {
                    pstm.setObject(i + 1, objects[i]);
                    pstm.addBatch();
                }
                if ((i + 1) % 1000 == 0) {
                    // 每执行一次
                    // 就清一次sql缓存，否则每一次是执行1000，2000，3000...条sql语句而不是每一次执行1000条。
                    pstm.executeBatch();
                    pstm.clearBatch();
                }
            }
            // 获得执行后影响的行数。形成一个数组
            int[] res = pstm.executeBatch();
            conn.commit();
            log.info("批量插入数据结果： " + Arrays.toString(res));
            log.info("批量操作插入总耗时： " + (System.currentTimeMillis() - startTime));
            return res;
        } catch (Exception e) {
            e.printStackTrace();
            log.error("批量插入数据异常： " + e);
            throw new RuntimeException(e);
        } finally {
            JdbcUtils.close(conn, pstm);
        }
    }

    /**
     * 批量操作（新增、修改、删除）,改操作入库较慢
     *
     * @param sqlList :查询语句集合
     * @return
     */
    public static void batch(List<String> sqlList) throws Exception {
        log.info("批量操作数据sql: " + sqlList);
        Long startTime = System.currentTimeMillis();
        Connection conn = null;
        Statement stmt = null;
        try {
            if (sqlList == null || sqlList.size() <= 0) {
                return;
            }
            conn = JdbcUtils.getConnection();
            conn.setAutoCommit(false);
            stmt = conn.createStatement();
            for (String sql : sqlList) {
                stmt.addBatch(sql);
            }
            // 获得执行后影响的行数。形成一个数组
            int[] result = stmt.executeBatch();
            log.error("批量插入数据结果： " + Arrays.toString(result));
            conn.commit();
        } catch (Exception e) {
            e.printStackTrace();
            log.error("批量插入数据异常： " + e);
            throw new RuntimeException(e);
        } finally {
            JdbcUtils.close(conn, stmt);
        }
        log.info("批量操作插入总耗时： " + (System.currentTimeMillis() - startTime));
    }


    /**
     * 更新数据库（新增、修改、删除）
     *
     * @param sql :查询语句
     * @return
     */
    public static int update(String sql) throws Exception {
        log.info("更新数据库（新增、修改、删除）数据sql: " + sql);
        Long startTime = System.currentTimeMillis();
        int result = update(sql, null);
        log.info("更新数据库总耗时： " + (System.currentTimeMillis() - startTime));
        return result;
    }

    /**
     * 更新数据库（新增、修改、删除）
     *
     * @param sql :查询语句
     * @return
     */
    public static int update(String sql, Object[] params) throws Exception {
        log.info("更新数据库（新增、修改、删除）数据sql: " + sql);
        Long startTime = System.currentTimeMillis();

        Integer result = execute(sql, params, new SQLCallBack<Integer>() {
            @Override
            public Integer execute(PreparedStatement pstm) throws SQLException {
                Integer result = pstm.executeUpdate();
                if (result > 0)
                    log.info("更新数据库成功！ ");
                return result;
            }
        });
        log.info("更新数据库总耗时： " + (System.currentTimeMillis() - startTime));
        return result;
    }

    abstract static class SQLCallBack<T> {
        // 批量更新、插入、删除
//        public abstract void batch(PreparedStatement pstm) throws SQLException;
        //改方法用户执行具体操作，包含查询，新增，修改，删除等
        public abstract T execute(PreparedStatement pstm) throws SQLException;
    }

    /**
     * 获取查询结果集合
     *
     * @param rs
     * @return
     * @throws SQLException
     */
    private static List<Map<String, Object>> getQueryResult(ResultSet rs) throws SQLException {
        List<Map<String, Object>> list = new ArrayList();
        if (rs == null)
            return null;
        ResultSetMetaData rsmd = rs.getMetaData();
        while (rs.next()) {
            Map<String, Object> map = new HashMap<String, Object>();
            for (int index = 1; index <= rsmd.getColumnCount(); index++) {
                String columnName = rsmd.getColumnName(index);
                Object columnValue = rs.getObject(index);
                map.put(columnName, columnValue);
            }
            list.add(map);
        }
        return list;
    }

    /**
     * 设置参数
     *
     * @param pstm
     * @param params
     * @throws SQLException
     */
    private static void setParams(PreparedStatement pstm, Object[] params) throws SQLException {
        if (params != null && params.length > 0) {
            for (int i = 0; i < params.length; i++) {
                pstm.setObject(i + 1, params[i]);
            }
        }
    }

    /**
     * 查询单条数据
     *
     * @param sql
     * @param params
     * @return
     * @throws Exception
     */
    public static Map<String, Object> executeQueryOne(String sql, Object[] params) throws Exception {
        List<Map<String, Object>> maps = executeQuery(sql, params);
        if (maps != null && maps.size() > 0) {
            return maps.get(0);
        }
        return null;
    }

    /**
     * 查询全部数据
     *
     * @param sql
     * @param params
     * @return
     * @throws Exception
     */
    public static List<Map<String, Object>> executeQuery(String sql, Object[] params) throws Exception {

        List<Map<String, Object>> list = new ArrayList();
        list = execute(sql, params, new SQLCallBack<List>() {
            @Override
            public List execute(PreparedStatement pstm) throws SQLException {
                ResultSet rs = pstm.executeQuery();
                List<Map<String, Object>> list = getQueryResult(rs);
                return list;
            }
        });
        return list;
    }

    /**
     * 提取公用方法
     *
     * @param sql
     * @param params
     * @param callback
     * @param <T>
     * @return
     * @throws Exception
     */
    public static <T> T execute(String sql, Object[] params, SQLCallBack<T> callback) throws Exception {

        T list = null;
        Connection conn = JdbcUtils.getConnection();
        PreparedStatement pstm = null;
        try {
            pstm = conn.prepareStatement(sql);
            setParams(pstm, params);
            list = callback.execute(pstm);
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        } finally {
            close(conn, pstm);
        }
        return list;
    }


    public static Integer executeUpdate(String sql, Object[] params) throws Exception {
        Integer id = null;
        Connection conn = JdbcUtils.getConnection();
        PreparedStatement pstm = null;
        try {
            //在preparedstatement里加入statement.RETURN_GENERATED_KEYS参数，代表需要获取自增主键值
            pstm = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            setParams(pstm, params);
            //在这里必须先提交修改，然后再获取主键，否则会报空指针异常
            pstm.executeUpdate();
            ResultSet resultSet = pstm.getGeneratedKeys();
            if (resultSet.next()) {
                id = resultSet.getInt(1);
            }
            return id;
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        } finally {
            try {
                if (pstm != null) pstm.close();
                if (conn != null) conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }


    public static void main(String[] args) throws Exception {

//        String sql = "select * from student";
        // List<Student> list = JdbcUtil.select(sql, Student.class);
        // List<Map<String, Object>> list2 = JdbcUtil.select(sql);
        //
        // Map<String, Object> map = JdbcUtil.selectOne(sql);
        // Student st = JdbcUtil.selectOne(sql, Student.class);
        // System.out.println(list);

//        String insertsql = "INSERT INTO student(id,name,sex,remark,time) VALUES(?,CONCAT('姓名',?),?,?,?)";
//        List<Object[]> params = new ArrayList<>();
//        params.add(new Object[]{1, 23, 43, 456, 56, 78, 8})
//        int[] batch = batch(insertsql, params);

//        // sql语句不再采用拼接方式，应用占位符问号的方式写sql语句。
//        sql = "delete from student where id=?";
//        int update = JdbcUtil.update(sql);

        String username = "admin";
        String sql = " select * from upms_user where username='" + username + "'";
        Map<String, Object> map = JdbcUtils.selectOne(sql);
        System.out.println("selectOne: " + map.toString());

        List<Map<String, Object>> list1 = JdbcUtils.select(sql);
        System.out.println("select: " + list1);

        String sql2 = " select * from upms_user where username=?";
        Map<String, Object> map2 = JdbcUtils.executeQueryOne(sql2, new String[]{username});
        System.out.println("executeQueryOne: " + map2);

        List<Map<String, Object>> list = JdbcUtils.executeQuery(sql2, new String[]{username});
        System.out.println("executeQuery: " + list);
    }
}
